package com.smartmail.receiving;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

import javax.mail.Address;
import javax.mail.MessagingException;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.smartmail.Attachment;
import com.smartmail.accounts.AccountUtility.IncomingMailServerProtocol;
import com.smartmail.accounts.MailAccount;
import com.smartmail.mailing.MailMessage;
import com.smartmail.mime.MessageIdentificator;
import com.smartmail.mime.MimeParser;
import com.smartmail.utility.MessageUtility;
import com.smartmail.utility.MimeMessageDecodeUtil;

/**
 * Incoming Mail's database Manager which is perfoming tasks
 * on database storing received mail messages.
 * 
 * @author Tomek
 * 
 */
public class IncomingMailDBManager extends SQLiteOpenHelper {


	/**
	 * Public constructor.
	 * 
	 * @param context - application context
	 * @param name	  - name of new database created
	 */
	public IncomingMailDBManager(Context context, String name) {

		super(context, name, null, _DB_VERSION);
		dbLock = new ReentrantLock();
	}
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

		db.execSQL("drop table if exists " + _INCOMING_MAIL_TABLE);
		db.execSQL("drop table if exists " + _TO_DELETE_MAIL_TABLE);
		onCreate(db);

	}


	/**
	 * Execute SQL command to create incoming mail table
	 * and also table of messages to delete.
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {

		String createCommand = "create table " + _INCOMING_MAIL_TABLE + " ("
				+ _INCOMING_MAIL_COLUMN_ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ _INCOMING_MAIL_HASH + " TEXT, "
				+ _INCOMING_MAIL_SENDER + " VARCHAR(50), "
				+ _INCOMING_MAIL_SENDER_PERSONAL + " TEXT, "
				+ _INCOMING_MAIL_COLUMN_RECEIPIENTS + " TEXT, "
				+ _INCOMING_MAIL_SUBJECT+ " TEXT, " 
				+ _INCOMING_MAIL_MSG_PATH + " TEXT, "
				+ _INCOMING_MAIL_SIZE + " INTEGER, " 
				+ _INCOMING_MAIL_STATE+ " INTEGER, " 
				+ _INCOMING_MAIL_SENT_TIMESTAMP + " INTEGER, "
				+ _INCOMING_MAIL_HAS_ATTACHMENTS + " INTEGER, "
				+ _INCOMING_MAIL_UID + " TEXT)";

		db.execSQL(createCommand);
		
		createCommand = "create table " + _TO_DELETE_MAIL_TABLE + " ("
				+ _TO_DELETE_MAIL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ _TO_DELETE_MESSAGE_UID + " TEXT, "
				+ _TO_DELETE_MESSAGE_HASH + " TEXT)";
		
		db.execSQL(createCommand);

	}

	/**
	 * Change state in database for given message.
	 * 
	 * @param newMessageState - new state of message
	 * @param messageHash - message hash which identifies message in database
	 * @return - true on success, otherwise false
	 */
	public boolean setState(int newMessageState, String messageHash) {

		if (isAlreadyInDB(messageHash)) {

			dbLock.lock();
			SQLiteDatabase db = getWritableDatabase();
			ContentValues args = new ContentValues();
			args.put(_INCOMING_MAIL_STATE, newMessageState);
			
			try{
				db.update(_INCOMING_MAIL_TABLE, args, _INCOMING_MAIL_HASH + "="
					+ "\"" + messageHash + "\"", null);
				
			} catch(Exception e){
				
				db.close();
				dbLock.unlock();
				return false;
			}
			
			dbLock.unlock();
			return true;
		} 
		else{
			return false;
		}

	}

	
	/**
	 * Change state in database for given message.
	 * 
	 * @param newMessageState - new state of message
	 * @param message - message which identifies message in database
	 * @return - true on success, otherwise false
	 */
	public boolean setState(int newMessageState, MimeMessage message) {

		boolean result = false;

		try {

			String messageHash = MessageUtility.getHash(message);
			result = setState(newMessageState, messageHash);

		} catch (MessagingException e) {

			Log.d(_logTag, "IncomingMailDBManager : error while setting new state");
			return false;
		}
		return result;

	}

	/**
	 * Update path info of message in database
	 * 
	 * @param path - path to file containing message content
	 * @param message - Mime Message object
	 * @return - true on success, otherwise false
	 */
	public boolean setPath(String path, MimeMessage message) {

		SQLiteDatabase db = null;
		try {
			if (isAlreadyInDB(message)) {

				dbLock.lock();
				db = getWritableDatabase();
				
				String messageHash = MessageUtility.getHash(message);
				ContentValues args = new ContentValues();
				args.put(_INCOMING_MAIL_MSG_PATH, path);
				
				db.update(_INCOMING_MAIL_TABLE, args, _INCOMING_MAIL_HASH
						+ "=" + "\"" + messageHash + "\"", null);

			} else{
				
				Log.d(_logTag,"isAlreadyInDB : false");
				return false;
			}

		} 
		catch (MessagingException e) {

			Log.d(_logTag, "IncomingMailDBManager : setPath");
			Log.d(_logTag, "IncomingMailDBManager: " + e.toString());
			
			if(db != null)
				db.close();
			dbLock.unlock();
			return false;
		}
		catch( Exception e){
			
			Log.d(_logTag, "IncomingMailDBManager : setPath");
			Log.d(_logTag, "IncomingMailDBManager: " + e.toString());
			
			if(db != null)
				db.close();
			dbLock.unlock();
			return false;
		}
		
		db.close();
		dbLock.unlock();
		return true;
	}
	

	
	/**
	 * Return last mail stored in incoming mails table
	 * (in order of sent_date column)
	 * 
	 * @return - last mail received and stored in database.
	 */
	public MailMessage getLastMail(){
		
		dbLock.lock();
		
		MailMessage message = null;
		SQLiteDatabase db = getReadableDatabase();

		Cursor cursor = null;
		try{
			cursor = db.query(_INCOMING_MAIL_TABLE, null, null, null, null,
				null, _INCOMING_MAIL_SENT_TIMESTAMP + " DESC LIMIT 1");
		
		} catch (Exception e){
			
			Log.d(_logTag, "IncomingMailDBManager : getLastMail");
			Log.d(_logTag, "IncomingMailDBManager: " + e.toString());
			
			if(cursor != null)
				cursor.close();
			db.close();
			dbLock.unlock();
			return null;
		}

		// db is empty, there are no emails messages
		if( !(cursor.getCount() > 0) ){
			
			cursor.close();
			db.close();
			dbLock.unlock();
			return null;
		}

		cursor.moveToFirst();
		message = retrieveMessageFromCursor(cursor);

		cursor.close();
		db.close();
		dbLock.unlock();

		return message;
		
	}

	/**
	 * write info (headers) about message in database
	 * 
	 * @param message
	 *            - mime message object
	 * @return - true on success
	 */
	
	/**
	 * Write message headers to database.
	 * 
	 * @param message - message object from which headers will be retrieved
	 * @param messageUID - UID field of given message
	 * @param mailAccount - mailAccount for which this message belongs
	 * @return - true on success, otherwise false
	 */
	public boolean writeMessageHeaders(MimeMessage message, String messageUID, MailAccount mailAccount) {

		dbLock.lock();
		SQLiteDatabase db = getWritableDatabase();

		ContentValues values = new ContentValues();

		try {
			values.clear();

			values.put(_INCOMING_MAIL_HASH, MessageUtility.getHash(message));

			// retrieve email address of sender
			Address[] froms = message.getFrom();
			if(froms.length == 0 || froms[0] == null){
				
				values.put(_INCOMING_MAIL_SENDER, "");
				values.put(_INCOMING_MAIL_SENDER_PERSONAL, "");
			}
			else{
				String email = ((InternetAddress) froms[0]).getAddress();
				if( email == null)
					email = "";
				values.put(_INCOMING_MAIL_SENDER, email);
				
				String personal = ((InternetAddress) froms[0]).getPersonal();
				if( personal == null)
					personal = "";
				values.put(_INCOMING_MAIL_SENDER_PERSONAL, personal);
			}
			
			// retrieve email subject
			String subject = MimeMessageDecodeUtil.getSubject(message);
			if(subject == null)
				subject = "";
			values.put(_INCOMING_MAIL_SUBJECT, subject);
			
			values.put(_INCOMING_MAIL_MSG_PATH, NO_PATH);
			values.put(_INCOMING_MAIL_STATE, MailMessage._MESSAGE_NOT_DOWNLOADED);
			
			// retrieve sent date, if it is impossible assume that
			// sent date is current time
			Date sentDate = message.getSentDate();
			if(sentDate == null)
				sentDate = new Date();
			long sentTimeStamp = sentDate.getTime() / 1000L;
			values.put(_INCOMING_MAIL_SENT_TIMESTAMP,  sentTimeStamp);
			
			
			// retrieve receipients of message
			Address[] rcps = message.getAllRecipients();
			String receipients = "";
			if( rcps != null && rcps.length > 0){
			
				for (Address address : rcps)
					receipients = receipients + " # " + address.toString();
			}
			values.put(_INCOMING_MAIL_COLUMN_RECEIPIENTS, receipients);
					
			// retrieve and set MessageUID
			if( messageUID == null)
				messageUID = "";
			values.put(_INCOMING_MAIL_UID, messageUID);
			
			
			// retrieve info about attachments existance
			int hasAttachments = 0;
			if(mailAccount.getIncomingMailServerProtocolType() == IncomingMailServerProtocol.IMAP){
				
				List<Attachment> attachments = new ArrayList<Attachment>();
				MimeParser.getAllAttachments(message, attachments);
				hasAttachments = (attachments.size() == 0 ? 0 : 1);
			}
			values.put(_INCOMING_MAIL_HAS_ATTACHMENTS, hasAttachments);

			
			//insert message headers to database
			db.insertOrThrow(_INCOMING_MAIL_TABLE, null, values);
			
		} catch (MessagingException e) {

			Log.d(_logTag, "IncomingMailDBManager : writeMessageHeaders");
			Log.d(_logTag, "IncomingMailDBManager: " + e.toString());
			
			//for(StackTraceElement elem : e.getStackTrace())
			//	Log.d(_logTag,elem.toString());
			
			db.close();
			dbLock.unlock();
			return false;
	
		} catch (Exception e){
			
			Log.d(_logTag, "IncomingMailDBManager : writeMessageHeaders");
			Log.d(_logTag, "IncomingMailDBManager: " + e.toString());
			
			//for(StackTraceElement el : e.getStackTrace())
			//	Log.d(_logTag, "IncomingMailDBManager : " + el.toString());
			
			db.close();
			dbLock.unlock();
			return false;	
		}
		

		db.close();
		dbLock.unlock();

		
		return true;

	}
	
	/**
	 * Add message to list of messages to be deleted from server.
	 * 
	 * @param mailMessage - mail Message object which identifies message in server
	 * @return - true on success, otherwise false
	 */
	public boolean addMessageToDeleteFromServer(MailMessage mailMessage){
		
		dbLock.lock();
		SQLiteDatabase db = getWritableDatabase();
		ContentValues values = new ContentValues();

		try {
			values.clear();

			values.put(_TO_DELETE_MESSAGE_HASH, mailMessage.getMessageHash());
			String messageUID = mailMessage.getMessageUID() == null ? "" : mailMessage.getMessageUID();
			values.put(_TO_DELETE_MESSAGE_UID, messageUID);

			//insert message identificator to database
			db.insertOrThrow(_TO_DELETE_MAIL_TABLE, null, values);
			
		} catch (Exception e){
			
			Log.d(_logTag, "IncomingMailDBManager : addMessageToDeleteFromServer");
			Log.d(_logTag, "IncomingMailDBManager: " + e.toString());
			
			db.close();
			dbLock.unlock();
			return false;	
		}
		

		db.close();
		dbLock.unlock();
		
		return true;

	}
	
	/**
	 * Returns identificators  (list of MessageIdenticator objects) of messages
	 * which are wanted to be deleted from server
	 * 
	 * @return - list of messages to be deleted from server identificators
	 */
	public List<MessageIdentificator> getIdentifiersOfMessagesToDelete(){
		
		List<MessageIdentificator> messageIdentificators = new ArrayList<MessageIdentificator>();
		
		dbLock.lock();
		SQLiteDatabase db = getReadableDatabase();
		
		Cursor cursor = null;
		try{
			cursor = db.query(_TO_DELETE_MAIL_TABLE,new String[]{ _TO_DELETE_MESSAGE_UID, _TO_DELETE_MESSAGE_HASH}, null, null, null,
				null, null);
		
		} catch(Exception e){
			
			Log.d(_logTag, "IncomingMailDBManager : getIdentifiersOfMessagesToDelete");
			Log.d(_logTag, "IncomingMailDBManager: " + e.toString());
			
			if(cursor!=null)
				cursor.close();
			db.close();
			dbLock.unlock();
			return null;
		}
		
		/**
		 * iterate whole cursor
		 */
		cursor.moveToFirst();
		while (cursor.isAfterLast() == false) {

			String messageUID = cursor.getString(0);
			String messageHash = cursor.getString(1);
			
			if(messageUID.equals(""))
				messageUID = null;
			
			MessageIdentificator mIdent = new MessageIdentificator(messageHash,messageUID);
			messageIdentificators.add(mIdent);
			
			cursor.moveToNext();
		}
		cursor.close();
		db.close();
		dbLock.unlock();

		return messageIdentificators;
		
	}
	
	/**
	 * Remove message from list of messages to be deleted from server.
	 * 
	 * @param messageIdent - message identificator object which identifies one message.
	 * @return - true on success, otherwise false
	 */
	public boolean removeFromToDeleteTable(MessageIdentificator messageIdent){
		
		dbLock.lock();
		SQLiteDatabase db = getReadableDatabase();
		
		try{
			String where = _TO_DELETE_MESSAGE_HASH + " = \"" + messageIdent.getMessageHash() + "\"";
			db.delete(_TO_DELETE_MAIL_TABLE, where, null);
		} catch(Exception e){
			
			Log.d(_logTag, "IncomingMailDBManager : deleteMessageFromDeleting");
			Log.d(_logTag, "IncomingMailDBManager: " + e.toString());
			db.close();
			dbLock.unlock();
			return false;
		}
		
		db.close();
		dbLock.unlock();
		return true;
		
	}
	
	/**
	 * 
	 * @return - list of all messages' unique identifiers UID
	 */
	
	/**
	 * Returns UID field's of all messages stored in incoming mails table.
	 * 
	 * @return - list containing UID field's of all messages stored in incoming mails table.
	 */
	public List<String>	getAllMessageUIDs(){
		
		dbLock.lock();

		List<String> messagesUIDs = new ArrayList<String>();
		SQLiteDatabase db = getReadableDatabase();

		Cursor cursor = null;
		try{
			cursor = db.query(_INCOMING_MAIL_TABLE,new String[]{ _INCOMING_MAIL_UID}, null, null, null,
				null, null);
		
		} catch(Exception e){
			
			Log.d(_logTag, "IncomingMailDBManager : getAllMessageUIDs");
			Log.d(_logTag, "IncomingMailDBManager: " + e.toString());
			
			if(cursor!=null)
				cursor.close();
			db.close();
			dbLock.unlock();
			return null;
		}

		/**
		 * iterate whole cursor
		 */
		cursor.moveToFirst();
		while (cursor.isAfterLast() == false) {

			String uid = cursor.getString(0);
			if(uid!=null)
				messagesUIDs.add(uid);
			cursor.moveToNext();
		}
		cursor.close();
		db.close();
		dbLock.unlock();

		return messagesUIDs;
		
	}


	/**
	 * Indicate whether given message is already stored in 
	 * INCOMING MAIL TABLE
	 * 
	 * @param messageHash - message hash which identifies message
	 * @return - true if given message is already stored in database, otherwise false
	 */
	public boolean isAlreadyInDB(String messageHash) {

		dbLock.lock();
		//Log.d(_logTag,"IncomingMailDBManager : isAlreadyInDB , messageHash - " + messageHash);
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = null;
		
		try{
			cursor = db.rawQuery("SELECT 1 FROM " + _INCOMING_MAIL_TABLE
				+ " WHERE " + _INCOMING_MAIL_HASH + " = " + "\"" + messageHash
				+ "\"", null);
		
		} catch(Exception e){
			
			Log.d(_logTag, "IncomingMailDBManager : isAlreadyInDB");
			Log.d(_logTag, "IncomingMailDBManager: " + e.toString());
			
			db.close();
			dbLock.unlock();
			return true;
		}

		boolean exists = (cursor.getCount() > 0);

		cursor.close();
		db.close();
		dbLock.unlock();

		return exists;

	}

	/**
	 * Indicate whether given message is already stored in 
	 * INCOMING MAIL TABLE
	 * 
	 * @param message - MimeMessage object which identifies message
	 * @return - true if given message is already stored in database, otherwise false
	 */
	public boolean isAlreadyInDB(MimeMessage message) {

		boolean result = false;
		try {
			String messageHash = MessageUtility.getHash(message);
			result = isAlreadyInDB(messageHash);
		} catch (MessagingException e) {

			Log.d(_logTag,"IncomingMailDBManager : isAlreadyInDB - messaging exception");
			return false;
		}

		return result;
	}

	/**
	 * Returns list of all received messages stored in device.
	 * 
	 * @return - list of all received messages stored in device.
	 */
	public List<MailMessage> getAllMessages() {

		dbLock.lock();

		List<MailMessage> messages = new ArrayList<MailMessage>();
		SQLiteDatabase db = getReadableDatabase();

		Cursor cursor = null;
		try{
		cursor = db.query(_INCOMING_MAIL_TABLE, null, null, null, null,
				null, _INCOMING_MAIL_SENT_TIMESTAMP + " DESC");
		
		} catch(Exception e){
			
			Log.d(_logTag, "IncomingMailDBManager : getAllMessages");
			Log.d(_logTag, "IncomingMailDBManager: " + e.toString());
			
			if(cursor!=null)
				cursor.close();
			db.close();
			dbLock.unlock();
			return null;
		}

		/**
		 * iterate whole cursor
		 */
		cursor.moveToFirst();
		while (cursor.isAfterLast() == false) {

			messages.add(retrieveMessageFromCursor(cursor));
			cursor.moveToNext();
		}
		cursor.close();
		db.close();
		dbLock.unlock();

		return messages;
	}

	/**
	 * @return - RFC ID of next message in database
	 * @param rfcID
	 */
	
	/**
	 * Returns MessageHash of next (in order of 'Sent Date') downloaded message 
	 * stored in database.
	 * 
	 * @param messageHash - message hash of base message
	 * @return - MessageHash of next (in order of 'Sent Date') downloaded message 
	 * stored in database. If such message dosn't exist, null is returned.
	 */
	public String getNextDownloadedMessage(String messageHash) {

		dbLock.lock();
		SQLiteDatabase db = getReadableDatabase();

		Cursor cursor = null;
		try{
			cursor = db.query(_INCOMING_MAIL_TABLE,
				new String[] { _INCOMING_MAIL_SENT_TIMESTAMP },
				_INCOMING_MAIL_HASH + " = " + "\"" + messageHash + "\"", null,
				null, null, null);
		
		} catch(Exception e){
			
			Log.d(_logTag, "IncomingMailDBManager : getNextMessageRFC_ID");
			Log.d(_logTag, "IncomingMailDBManager: " + e.toString());
			
			if(cursor!=null)
				cursor.close();
			db.close();
			dbLock.unlock();
			return null;
		}

		if (cursor.getCount() != 1) {

			cursor.close();
			db.close();
			dbLock.unlock();
			return null;
		}

		cursor.moveToFirst();
		int dateTimestamp = cursor.getInt(0);
		cursor.close();
		cursor = null;

		/**
		 * get info about messages sorted by sent timestamp
		 */
		try{
			cursor = db.query(_INCOMING_MAIL_TABLE, new String[] {
				_INCOMING_MAIL_HASH, _INCOMING_MAIL_STATE },
				_INCOMING_MAIL_SENT_TIMESTAMP + " > " + dateTimestamp, null,
				null, null, _INCOMING_MAIL_SENT_TIMESTAMP);
		
		} catch(Exception e){
			
			Log.d(_logTag, "IncomingMailDBManager : getNextMessageRFC_ID");
			Log.d(_logTag, "IncomingMailDBManager: " + e.toString());
			
			if(cursor!=null)
				cursor.close();
			db.close();
			dbLock.unlock();
			return null;
		}
		
		/**
		 * iterate whole cursor
		 */
		cursor.moveToFirst();
		while (cursor.isAfterLast() == false) {

			int state = cursor.getInt(1);
			if (state == MailMessage._MESSAGE_DOWNLOADED_NOT_READ
					|| state == MailMessage._MESSAGE_DOWNLOADED_READ) {

				String result = cursor.getString(0);
				cursor.close();
				db.close();
				dbLock.unlock();
				
				return result;
			}

			cursor.moveToNext();
		}
		
		cursor.close();
		db.close();
		dbLock.unlock();

		return null;
	}

	/**
	 * Returns MessageHash of previous (in order of 'Sent Date') downloaded message 
	 * stored in database.
	 * 
	 * @param messageHash - message hash of base message
	 * @return - MessageHash of previous (in order of 'Sent Date') downloaded message 
	 * stored in database. If such message dosn't exist, null is returned.
	 */
	public String getPreviousMessageHash(String messageHash) {

		dbLock.lock();
		SQLiteDatabase db = getReadableDatabase();

		Cursor cursor = null;
		try{
			cursor = db.query(_INCOMING_MAIL_TABLE,
				new String[] { _INCOMING_MAIL_SENT_TIMESTAMP },
				_INCOMING_MAIL_HASH + " = " + "\"" + messageHash + "\"", null,
				null, null, null);
		
		} catch(Exception e){
			
			Log.d(_logTag, "IncomingMailDBManager : getPreviousMessageRFC_ID");
			Log.d(_logTag, "IncomingMailDBManager: " + e.toString());
			
			if(cursor!=null)
				cursor.close();
			db.close();
			dbLock.unlock();
			return null;
		}

		if (cursor.getCount() != 1) {

			cursor.close();
			db.close();
			dbLock.unlock();
			return null;
		}

		cursor.moveToFirst();
		int dateTimestamp = cursor.getInt(0);
		cursor.close();
		cursor = null;

		/**
		 * get info about messages sorted by sent timestamp
		 */
		try{
			cursor = db.query(_INCOMING_MAIL_TABLE, new String[] {
				_INCOMING_MAIL_HASH, _INCOMING_MAIL_STATE },
				_INCOMING_MAIL_SENT_TIMESTAMP + " < " + dateTimestamp, null,
				null, null, _INCOMING_MAIL_SENT_TIMESTAMP + " DESC");
		
		} catch(Exception e){
			
			Log.d(_logTag, "IncomingMailDBManager : getPreviousMessageRFC_ID");
			Log.d(_logTag, "IncomingMailDBManager: " + e.toString());
			
			if(cursor!=null)
				cursor.close();
			db.close();
			dbLock.unlock();
			return null;
		}
		
		/**
		 * iterate whole cursor
		 */
		cursor.moveToFirst();
		while (cursor.isAfterLast() == false) {

			int state = cursor.getInt(1);
			if (state == MailMessage._MESSAGE_DOWNLOADED_NOT_READ
					|| state == MailMessage._MESSAGE_DOWNLOADED_READ) {

				String result = cursor.getString(0);
				cursor.close();
				db.close();
				dbLock.unlock();
				
				return result;
			}

			cursor.moveToNext();
		}
		
		cursor.close();
		db.close();
		dbLock.unlock();

		return null;
	}

	/**
	 * 
	 * @param rfcID
	 *            - globally unique identifier of email message
	 * @return - MailMessage object retrieved from the database
	 */
	
	/**
	 * Retrieve MailMessage object from data base.
	 * 
	 * @param messageHash - message hash which uniquely identifies message
	 * @return - Mail Message object, or null when any message stored in database
	 * has given message hash
	 */
	public MailMessage getMessage(String messageHash) {
		
		dbLock.lock();
		
		String selection = _INCOMING_MAIL_HASH + " = " + "\"" + messageHash + "\"";
		SQLiteDatabase db = getReadableDatabase();
		
		Cursor cursor = null;
		try{
			cursor = db.query(_INCOMING_MAIL_TABLE, null, selection, null,
				null, null, null);
			
		} catch(Exception e){
			
			Log.d(_logTag, "IncomingMailDBManager : getMessage");
			Log.d(_logTag, "IncomingMailDBManager: " + e.toString());
			
			if(cursor!=null)
				cursor.close();
			db.close();
			dbLock.unlock();
			return null;
		}

		if (cursor.moveToFirst() == false) {
			
			cursor.close();
			db.close();
			dbLock.unlock();
			return null;
		}

		/**
		 * there should be only one row in cursor, because rfcID is globally
		 * unique identifier
		 */
		MailMessage message = retrieveMessageFromCursor(cursor);

		if (cursor.getCount() > 1)
			Log.wtf(RetrievalService._logTag,
					"more than one mail message with the same rfcID in database!");

		cursor.close();
		db.close();
		dbLock.unlock();

		return message;
	}
	
	/**
	 * Delete from incoming mails table row with given message.
	 * 
	 * @param messageHash -  message hash which uniquely identifies message
	 * @return - true on success, otherwise false.
	 */
	public boolean deleteMessage(String messageHash){
		
		dbLock.lock();
		SQLiteDatabase db = getReadableDatabase();
		
		try{
			String where = _INCOMING_MAIL_HASH + " = \"" + messageHash + "\"";
			db.delete(_INCOMING_MAIL_TABLE, where, null);
		} catch(Exception e){
			
			Log.d(_logTag, "IncomingMailDBManager : deleteMessage");
			Log.d(_logTag, "IncomingMailDBManager: " + e.toString());
			db.close();
			dbLock.unlock();
			return false;
		}
		
		db.close();
		dbLock.unlock();
		return true;

	}
	
	/**
	 * Delete all tables.
	 */
	public void deleteDatabase(){
		
		dbLock.lock();
		SQLiteDatabase db = getReadableDatabase();
		
		db.execSQL("drop table if exists " + _INCOMING_MAIL_TABLE);
		db.execSQL("drop table if exists " + _TO_DELETE_MAIL_TABLE);
		
		db.close();
		dbLock.unlock();
	}

	/**
	 * 
	 * @param cursor
	 * @return - MailMessage retrieved from the row pointed by cursor
	 */
	
	/**
	 * Create Mail Message object from one incoming table row.
	 * 
	 * @param cursor - cursor object pointing at certain row.
	 * @return - Mail Message object create from cursor.
	 */
	private MailMessage retrieveMessageFromCursor(Cursor cursor) {

		if (cursor.isAfterLast())
			return null;

		long dbID = cursor.getLong(0);
		String messageHash = 	( cursor.getString(1).equals("") ? null : cursor.getString(1) );
		String sender = 		( cursor.getString(2).equals("") ? null : cursor.getString(2) );
		String senderPersonal = ( cursor.getString(3).equals("") ? null : cursor.getString(3) );
		String receipients = 	( cursor.getString(4).equals("") ? null : cursor.getString(4) );
		String subject = 		( cursor.getString(5).equals("") ? null : cursor.getString(5) );
		String msgPath = null;
		try {
			msgPath =	        ( cursor.getString(6).equals("") ? null : cursor.getString(6) );
		}
		catch (Exception e){
			
		}
		
		int msgSize = 			cursor.getInt(7);
		int state = 			cursor.getInt(8);
		int sentTimeStamp = 	cursor.getInt(9);
		boolean hasAttachments = (cursor.getInt(10) == 0 ? false : true);
		String messageUID = 	cursor.getString(11);

		MailMessage message = new MailMessage(subject, null, sender, null);

		if( receipients != null){
			
			String receipientsParts[] = receipients.split(" #");
			// element at 0 index is an empty string
			for (int i = 1; i < receipientsParts.length; i++)
				message.addReceiver(receipientsParts[i]);
		}

		message.setDbID(dbID);
		message.setMessageHash(messageHash);
		message.setMsgPath(msgPath);
		message.setMsgSize(msgSize);
		message.setState(state);
		message.setSenderPersonal(senderPersonal);
		message.setHasAttachments(hasAttachments);
		
		long unixDate = (long)sentTimeStamp * 1000;
		message.setSentDate(new Date(unixDate));
		message.setMessageUID(messageUID);

		return message;

	}
	
	/**
	 * log tag for logging purposes
	 */
	public static String _logTag = "com.smartmail.receiving";

	/**
	 * Value of Path-Column for message which is not stored yet in device's memory
	 */
	public static String NO_PATH = "";

	/**
	 * Lock for the concurrent use of this database manager
	 */
	private Lock dbLock;
	/**
	 * DataBase Version number
	 */
	private static final int _DB_VERSION = 22;

	/**
	 * Name of the table which stores incoming messages
	 */
	private static final String _INCOMING_MAIL_TABLE = "incoming_mail";
	
	/**
	 * Name of the table which stores info about message identificators of messages
	 * to be deleted from server
	 */
	private static final String _TO_DELETE_MAIL_TABLE = "to_delete_mails";

	/**
	 * Names of the columns of incoming mail table
	 */
	private static final String _INCOMING_MAIL_COLUMN_ID = "incoming_mail_id";
	private static final String _INCOMING_MAIL_HASH = "incoming_mail_hash";
	private static final String _INCOMING_MAIL_SENDER = "sender_mail";
	private static final String _INCOMING_MAIL_SENDER_PERSONAL = "sender_mail_personal";
	private static final String _INCOMING_MAIL_COLUMN_RECEIPIENTS = "receipients";
	private static final String _INCOMING_MAIL_SUBJECT = "subject";
	private static final String _INCOMING_MAIL_MSG_PATH = "msg_path";
	private static final String _INCOMING_MAIL_SIZE = "msg_size";
	private static final String _INCOMING_MAIL_STATE = "state";
	private static final String _INCOMING_MAIL_SENT_TIMESTAMP = "send_timestamp";
	private static final String _INCOMING_MAIL_UID = "mail_uid";
	private static final String _INCOMING_MAIL_HAS_ATTACHMENTS = "has_attachments";
	

	/**
	 * Names of the colums of to delete mail table
	 */
	private static final String _TO_DELETE_MAIL_ID = "to_delete_mail_id";
	private static final String _TO_DELETE_MESSAGE_HASH = "to_delete_message_hash";
	private static final String _TO_DELETE_MESSAGE_UID = "to_delete_message_uid";

}
